-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateProfileUser`(IN p_userId BIGINT,IN p_parentProfId BIGINT,IN p_oldRootProfId BIGINT,IN p_firstName VARCHAR(45) , IN p_lastName VARCHAR(45),IN p_address TEXT,IN p_birthDate TIMESTAMP,IN p_gender VARCHAR(10),IN p_isDeath VARCHAR(10),IN p_deathDt TIMESTAMP,IN p_socialStatus VARCHAR(10),IN p_acadimicQual VARCHAR(10),IN p_country VARCHAR(45),IN p_city VARCHAR(45),IN p_blodType VARCHAR(10),IN p_religion VARCHAR(10),IN p_image MEDIUMBLOB,IN p_imageType varchar(45))
BEGIN
DECLARE rowcount INT;					
INSERT INTO `profiles`
(
`userId`,
`parentProfId`,
`firstName`,
`lastName`,
`address`,
`birthDate`,
`gender`,
`isDeath`,
`deathDt`,
`socialStatus`,
`acadimicQual`,
`ceatedDT`,
`country`,
`city`,
`blodType`,
`religion`,
`image`,
`imageType`)
VALUES
(
p_userId,
p_parentProfId,
p_firstName,
p_lastName,
p_address,
p_birthDate,
p_gender,
p_isDeath,
p_deathDt,
p_socialStatus,
p_acadimicQual,
CURRENT_TIMESTAMP,
p_country,
p_city,
p_blodType,
p_religion,
p_image,
p_imageType
);

SELECT row_count() INTO rowcount;
 
IF (p_oldRootProfId!= -1 AND  rowcount>0) THEN
    UPDATE `profiles`
    SET
    `parentProfId` = LAST_INSERT_ID()
    WHERE `profId` = p_oldRootProfId;
END IF;

END
